<template>
  <div class="panel">
    <ul class="panel-link">
      <li>
        <a href="#1">Oracle</a>
        <ul>
          <li><a href="#11">1. 数据库用户解锁</a></li>
          <li><a href="#12">2. 数据库表解锁</a></li>
          <li><a href="#13">3. 数据表空间查询及扩展</a></li>
          <li><a href="#13">3. 数据表空间查询及扩展</a></li>
          <li><a href="#14">4. 数据库导入导出</a></li>
          <li><a href="#15">5. 数据库用户密码有效期问题</a></li>
        </ul>
      </li>
      <li>
        <a href="#2">Mysql</a>
        <ul>
          <li><a href="#21">1. 数据库字符集修改</a></li>
          <li><a href="#22">2. 慢查询sql分析</a></li>
        </ul>
      </li>
    </ul>
    <div class="panel-item" id="11">
      <h3 class="title-h3" @click="toggle">Oracle
        <Icon type="ios-arrow-down"></Icon>
      </h3>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="11">1. 数据库用户解锁</h4>
        <pre>
          <p>-----查询被锁用户</p>
          <p>select * from dba_users where account_status &lt;&gt; 'OPEN';</p>
          <p>select * from dba_users where account_status = 'LOCKED';</p>
          <p>-----解锁用户</p>
          <p>alter user user_name account unlock;</p>
        </pre>
      </div>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="12">2. 数据库表解锁</h4>
        <pre>
          <p>-----先查询那些表被锁定了</p>
          <p>select b.owner,b.object_name,a.session_id,a.locked_mode</p>
          <p>from v$locked_object a,dba_objects b</p>
          <p>where b.object_id = a.object_id;</p>
          <p>-----查询出被锁定的表的 sid 和serial# 然后kill</p>
          <p>select b.username,b.sid,b.serial#,logon_time </p>
          <p>from v$locked_object a,v$session b</p>
          <p>where a.session_id = b.sid and sid in(</p>
          <p>select a.SESSION_ID</p>
          <p>from v$locked_object a,dba_objects b</p>
          <p>where b.object_id = a.object_id) order by b.logon_time;</p>
          <p>-----解锁（589 是sid,2693是serial）</p>
          <p>alter system kill session '157,52055' immediate;</p>
        </pre>
      </div>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="13">3. 数据表空间查询及扩展</h4>
        <pre>
          <p>-----查看数据库表空间使用情况</p>
          <p>SELECT a.tablespace_name "表空间名", </p>
          <p>total / (1024 * 1024 )|| 'M' "表空间大小(M)", </p>
          <p>free / (1024 * 1024 )|| 'M' "表空间剩余大小(M)", </p>
          <p>(total - free) / (1024 * 1024 )|| 'M' "表空间使用大小(M)", </p>
          <p>round((total - free) / total, 4) * 100 "使用率 %" </p>
          <p>FROM (SELECT tablespace_name, SUM(bytes) free </p>
          <p>FROM dba_free_space </p>
          <p>GROUP BY tablespace_name) a, </p>
          <p>(SELECT tablespace_name, SUM(bytes) total </p>
          <p>FROM dba_data_files </p>
          <p>GROUP BY tablespace_name) b </p>
          <p>WHERE a.tablespace_name = b.tablespace_name;</p>
        </pre>
        <p>表空间不够可以通过修改数据文件大小限制或者增加数据文件解决</p>
        <pre>
          <p>----修改表空间大小</p>
          <p>alter database DATAFILE '/oracle/oracle/oradata/orcl/orclpdb/data_mes.dbf'  autoextend on maxsize unlimited;</p>
          <p>----增加数据文件</p>
          <p>alter tablespace DATA_MES add datafile '/oracle/oracle/oradata/orcl/orclpdb/data_mes02.dbf' size 500M autoextend on maxsize 30000M;</p>
        </pre>
      </div>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="14">4. 数据库导入导出</h4>
        <p>通过exp/imp进行备份恢复</p>
        <pre>
          <p>exp systemmgmt/Abc123@XE file=/u01/app/oracle/xhr/systemmgmt1122.dmp full=y;</p>
          <p>imp systemmgmt/Abc123@XE file=/u01/app/oracle/xhr/systemmgmt1122.dmp full=y;</p>
        </pre>
        <p>通过expdb/impdb进行备份恢复</p>
        <pre>
          <p>expdp system/zlst1234@ORCLPDB schemas=systemmgmt dumpfile=zlst_systemmgmt_20180828.dmp logfile=zlst_systemmgmt_20180828.log DIRECTORY=DMP_DIR;</p>
          <p>impdp system/zlst1234@orclpdb DIRECTORY=DMP_DIR DUMPFILE=zlst_systemmgmt_20180828.dmp SCHEMAS=systemmgmt;</p>
        </pre>
      </div>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="15">5. 数据库用户密码有效期问题</h4>
        <pre>
          <p>-----查看有效期</p>
          <p>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';  </p>
          <p>----设置密码永不失效</p>
          <p>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;    </p>
          <p>----修改密码</p>
          <p>alter user systemmgmt identified by "zlst1234";</p>
        </pre>
      </div>
    </div>
    <div class="panel-item">
      <h3 class="title-h3" id="2" @click="toggle">Mysql
        <Icon type="ios-arrow-down"></Icon>
      </h3>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="21">1. 数据库字符集修改</h4>
        <p>一般要求mysql服务端的字符集为UTF-8，修改配置文件/etc/my.cnf，在[mysqld]下增加如下配置后重启mysql服务</p>
        <pre>
          <p>character_set_server=utf8</p>
          <p>lower_case_table_names=1</p>
        </pre>
        <img src="/images/help/5.2.1.png" class="img-lg" />
      </div>
      <div class="panel-ques">
        <h4 class="zl-title title-h4" id="22">2. 慢查询sql分析</h4>
        <p>问题背景：数据库性能下降，想找到哪些sql耗时较长，可以通过慢查询日志，设置慢查询阈值，帮助调优分析</p>
        <p>可以配置文件/etc/my.cnf在[mysqld]中添加如下：</p>
        <pre>
          <p>slow_query_log = 1  #是否开启慢查询</p>
          <p>slow_query_log_file=/data/log/mysql/db-slow.log   #慢日志存放路径</p>
          <p>long_query_time=1 #慢查询阀值</p>
        </pre>
        <img src="/images/help/5.2.3.png" class="img-lg" />
      </div>
    </div>
  </div>
</template>
<script type="text/javascript">
export default {
  data() {
    return {
    }
  },
  methods: {
    toggle(event) {
      if (event.currentTarget.parentNode.className.includes('collapse')) {
        event.currentTarget.parentNode.className = 'panel-item'
      } else {
        event.currentTarget.parentNode.className = 'panel-item collapse'
      }
    }
  }
}
</script>
<style rel="stylesheet/scss" lang="scss" scoped>
</style>
